package com.kecheng.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kecheng.pojo.Banner;
import com.kecheng.pojo.Guanzhu;
import com.kecheng.pojo.Keyword;

import com.kecheng.pojo.Peizhi;
import com.kecheng.pojo.Product;
import com.kecheng.util.DBUtil;


public class IndexDao {
	
	public List<Banner> findbybanner() throws Exception{
		List<Banner> banners = new ArrayList<Banner>();
		
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rst = null;
		try {
			 conn = DBUtil.getConnection();
			stat = conn.prepareStatement("select * " +
					"from banner order by orderid asc");
			rst = stat.executeQuery();
			while(rst.next()){
				Banner b = new Banner();
				b.setId(rst.getInt("id"));
				b.setImg(rst.getString("img"));
				b.setLink(rst.getString("link"));
				b.setOrderid(rst.getInt("orderid"));
				banners.add(b);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(rst != null){
				rst.close();
			}
			if(stat != null){
				stat.close();
			}
			DBUtil.close(conn);
		}
		return banners;

	}
	
//	public List<Classify> findbyclassify() throws Exception{
//		List<Classify> classifys = new ArrayList<Classify>();
//
//		Connection conn = null;
//		PreparedStatement stat = null;
//		ResultSet rst = null;
//		try {
//			 conn = DBUtil.getConnection();
//			stat = conn.prepareStatement("select * " +
//					"from classify order by orderid asc");
//			rst = stat.executeQuery();
//			while(rst.next()){
//				Classify c = new Classify();
//				c.setId(rst.getInt("id"));
//				c.setCname(rst.getString("cname"));
//				c.setOrderid(rst.getInt("orderid"));
//				classifys.add(c);
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//			throw e;
//		}finally{
//			if(rst != null){
//				rst.close();
//			}
//			if(stat != null){
//				stat.close();
//			}
//			DBUtil.close(conn);
//		}
//		return classifys;
//	}
	
	
	

	
	public Peizhi findpeizhi() throws Exception{
	
		int id=1;
		String sql="select * from peizhi where id=?";
		DBUtil db=new DBUtil();
		Peizhi peizhi = null;
		
		try {
			Connection conn=DBUtil.getConnection();
			PreparedStatement preStmt =conn.prepareStatement(sql);
			preStmt.setInt(1, id);
			ResultSet rs=preStmt.executeQuery();
			while(rs.next()){
				peizhi=new Peizhi();
			    peizhi.setId(rs.getInt("id"));
			    peizhi.setGzname(rs.getString("gzname"));
			    peizhi.setGzid(rs.getString("gzid"));
			    peizhi.setWeixin(rs.getString("weixin"));
			    peizhi.setAppid(rs.getString("appid"));
			    peizhi.setAppsecret(rs.getString("appsecret"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
	
	     }
		  return peizhi;	
	}
	
	public List<Product> showAll() throws Exception{
		List<Product> products=new ArrayList<Product>();
		String sql="select * from product where classify=1 order by id desc LIMIT 16 ";
		DBUtil db=new DBUtil();
		
		try {
			Connection conn=DBUtil.getConnection();
			PreparedStatement preStmt =conn.prepareStatement(sql);
			ResultSet rs=preStmt.executeQuery();
			while(rs.next()){
				Product product=new Product();
				product.setId(rs.getInt("id"));
		//		product.setImg(rs.getString("img"));
		//		product.setPrice(rs.getDouble("price"));
				product.setTitle(rs.getString("title"));
				products.add(product);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}
	    return products;
	}
	
	public List<Product> showAll1() throws Exception{
		List<Product> products=new ArrayList<Product>();
		String sql="select * from product where classify=7 order by id desc LIMIT 4 ";
		DBUtil db=new DBUtil();
		
		try {
			Connection conn=DBUtil.getConnection();
			PreparedStatement preStmt =conn.prepareStatement(sql);
			ResultSet rs=preStmt.executeQuery();
			while(rs.next()){
				Product product=new Product();
				product.setId(rs.getInt("id"));
			//	product.setImg(rs.getString("img"));
			//	product.setPrice(rs.getDouble("price"));
				product.setTitle(rs.getString("title"));
				products.add(product);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}
	    return products;
	}
	
	
	

//	public List<Order1> findbyOid(String oid){
//
//		List<Order1> order1s=new ArrayList<Order1>();
//		String sql = "select * from order1 where oid="+oid;
//		Connection conn=null;
//		DBUtil db = new DBUtil();
//		try {
//			conn = db.getConnection();
//			PreparedStatement ps=null;
//			ps = conn.prepareStatement(sql);
//			ResultSet rs=ps.executeQuery();
//			while(rs.next()){
//				Order1 order1=new Order1();
//				order1.setId(rs.getInt("id"));
//				order1.setOid(rs.getString("oid"));
//
//
//				order1.setPrice(rs.getDouble("price"));
//
//
//				order1s.add(order1);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		db.close(conn);
//		return order1s;
//
//	}
//
//
//	public void addorder1(Order1 order1){
//
//		PreparedStatement stat = null;
//		ResultSet rst = null;
//
//
//		DBUtil db = new DBUtil();
//		Connection conn=null;
//
//		int i;
//		try {
//			conn = (Connection) db.getConnection();
//			 stat  = (PreparedStatement) conn.prepareStatement("insert into order1(oid,num,title,price,img,color,size) values(?,?,?,?,?,?,?)");
//			 stat .setString(1, order1.getOid());
//
//
//			 stat .setDouble( 4,order1.getPrice());
//
//
//			 stat .executeUpdate();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		db.close(conn);
//
//
//	}
	
	


public void updateorder(int id,String status) throws SQLException {

		String sql="delete from order where id=?";
		DBUtil db=new DBUtil();
		try {
			Connection conn=db.getConnection();
			PreparedStatement preStmt =conn.prepareStatement(sql);
			preStmt.setInt(1, id);			
		} catch (SQLException e) {
			
			e.printStackTrace();
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
	}


public Guanzhu findguanzhu() throws Exception {
	// TODO Auto-generated method stub

	int id=1;
	String sql="select * from guanzhu where id=?";
	DBUtil db=new DBUtil();
	Guanzhu guanzhu = null;
	
	try {
		Connection conn=(Connection) db.getConnection();
		PreparedStatement preStmt =(PreparedStatement) conn.prepareStatement(sql);
		preStmt.setInt(1, id);
		ResultSet rs=(ResultSet) preStmt.executeQuery();
		while(rs.next()){
			guanzhu=new Guanzhu();
			guanzhu.setId(rs.getInt("id"));
			guanzhu.setTitle(rs.getString("title"));
			guanzhu.setMiaoshu(rs.getString("miaoshu"));
			guanzhu.setContent(rs.getString("content"));
			guanzhu.setImg(rs.getString("img"));

		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();

     }
	  return guanzhu;	
}


public Keyword findkeyword(String content) throws Exception {
	// TODO Auto-generated method stub
	

	
	String sql="select * from keyword where keyword=?";
	DBUtil db=new DBUtil();
	Keyword keyword = null;
	
	try {
		Connection conn=(Connection) db.getConnection();
		PreparedStatement preStmt =(PreparedStatement) conn.prepareStatement(sql);
		preStmt.setString(1, content);
		ResultSet rs=(ResultSet) preStmt.executeQuery();
		while(rs.next()){
			keyword=new Keyword();
			keyword.setId(rs.getInt("id"));
			keyword.setTitle(rs.getString("title"));
			keyword.setMiaoshu(rs.getString("miaoshu"));
			keyword.setContent(rs.getString("content"));
			keyword.setImg(rs.getString("img"));
			keyword.setKeyword(rs.getString("keyword"));

		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();

     }
	  return keyword;	
}


public int countteam() throws Exception {
	// TODO Auto-generated method stub
	int totalRows = 0; //总的记录数
	Connection conn = null;
	PreparedStatement stat = null;
	ResultSet rst = null;
	try {
		conn = DBUtil.getConnection();
		stat = conn.prepareStatement("select count(*) from team");
		rst = stat.executeQuery();
		if(rst.next()){
			totalRows = rst.getInt(1);
		}
		
	}catch (Exception e) {
		e.printStackTrace();
		throw e;
	}finally{
		if(rst != null){
			rst.close();
		}
		if(stat != null){
			stat.close();
		}
		DBUtil.close(conn);
	}
	 return totalRows;
	
	
	
}



}


